package linuxbox.todo.bean;

import java.util.Vector;
import java.util.Iterator;

import linuxbox.exception.*;
import linuxbox.bean.*;
import linuxbox.util.*;

import linuxbox.todo.logic.BoxTodoEntry;

public class BoxTodoEntryBean extends BoxTodoEntry implements ITableBean
{
  protected DbAdapter m_db = null;
  protected boolean m_bTransaction = false;
	static protected String m_TableName = "box_todo";

	public BoxTodoEntryBean(BaseIntKey pk, DbAdapter db)
	{
		this.init(pk, db);
	}

	public BoxTodoEntryBean(BaseIntKey pk)
	{
		this.init(pk, null);
	}

	public void init(BaseIntKey pk, DbAdapter db)
	{
		if (db != null)
		{
			m_db = db;
			m_bTransaction = true;
		}
		if (pk != null)
			setID(pk.getID());
	}

  public String getTableName()
  {
    return m_TableName;
  }

  public void setTableName(String str)
  {
    m_TableName = str;
  }

	public void makeConnection() throws Exception
	{
		if ( m_db == null )
      m_db = new DbAdapter(true);
	}

	public void closeConnection() throws Exception
	{
	  if (m_db != null)
	  {
	    m_db.close();
	    m_db = null;
	  }
	}

	public void load() throws Exception
	{
		StringBuffer sql = new StringBuffer();

		sql.append("select id, userid, subject, priority, complete, category, description, startdate, duedate, assignedby from ");
    sql.append(getTableName());
    sql.append(" where id = " + getID());

    makeConnection();
		m_db.executeQuery(sql.toString());
		if (m_db.next())
		{
	    m_iID = m_db.getInt("id");
	    m_UserID = m_db.getInt("userid");
	    m_sSubject = m_db.getString("subject");
	    m_iPriority = m_db.getInt("priority");
	    m_sCategory = m_db.getString("category");
	    m_bComplete = (m_db.getInt("complete") != 0);
      m_sDescription = m_db.getString("description");
      m_sAssignedBy = m_db.getString("assignedby");
      m_StartDate = m_db.getTimestamp("startdate");
      m_DueDate = m_db.getTimestamp("duedate");
		}
		else
		{
			if (!m_bTransaction)
			  closeConnection();
			throw new LinuxboxException(ExceptionHelper.TODO, "LOAD", "Load error:  m_iID=" + m_iID);
		}
		if (!m_bTransaction)
			closeConnection();
	}

	public void store() throws Exception
	{
	  StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(getTableName());
		sql.append(" set ");
		DBUtil.setInteger(sql, "priority", m_iPriority, true);
		DBUtil.setInteger(sql, "complete", ((m_bComplete)?1:0), false);
		DBUtil.setString(sql, "category", m_sCategory, false);
		DBUtil.setInteger(sql, "userid", m_UserID, false);
		DBUtil.setString(sql, "subject", m_sSubject, false);
		DBUtil.setString(sql, "description", m_sDescription, false);
		DBUtil.setString(sql, "assignedby", m_sAssignedBy, false);
		DBUtil.setTimestamp(sql, "startdate", m_StartDate, false);
		DBUtil.setTimestamp(sql, "duedate", m_DueDate, false);

		sql.append(" where id = " + m_iID);

//System.out.println(sql);

		makeConnection();
		m_db.executeUpdate(sql.toString());
		if (!m_bTransaction)
			closeConnection();
	}

	public void refresh() throws Exception
	{
		load();
	}

	public void create() throws Exception
	{
		if (m_iID != 0)
		{
      String sql = "INSERT INTO ";
      sql += getTableName();
      sql += " (id) VALUES(" + m_iID + ")";
			makeConnection();
			m_db.executeUpdate(sql);
		}
		else
		{
			String sql = "select max(id) from ";
      sql += getTableName();
			makeConnection();
			m_db.executeQuery(sql);
			if (m_db.next())
			{
        int max_id = m_db.getInt(1);
        max_id++;
        m_iID = max_id;
			}
      else
      {
        m_iID = 1;
      }
      sql = "insert into ";
      sql += getTableName();
      sql += " (id) values(" + m_iID + ")";
      m_db.executeUpdate(sql);
		}
	  if (!m_bTransaction)
  		closeConnection();
	}

	public void remove() throws Exception
	{
		String sql = "DELETE FROM ";
    sql += getTableName();
    sql += (" where id = " + m_iID);
		makeConnection();
		m_db.executeUpdate(sql);
		if (!m_bTransaction)
			closeConnection();
	}

	public Object findByKey() throws Exception
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select id from ");
    sql.append(getTableName());
    sql.append(" where id = " + m_iID);
		makeConnection();
		m_db.executeQuery(sql.toString());
		BaseIntKey pk = null;
		if(m_db.next())
		{
			pk = new BaseIntKey();
			pk.setID(m_db.getInt(1));
		}
		if (!m_bTransaction)
			closeConnection();
		return pk;
	}

	public Iterator findBySql(String sqlWhere) throws Exception
	{
		Vector v = null;
		try
		{
		  StringBuffer sql = new StringBuffer();
		  sql.append("select id from ");
      sql.append(getTableName());
      sql.append(sqlWhere);
//System.out.println(sql);
			makeConnection();
			m_db.executeQuery(sql.toString());
			BaseIntKey pk = null;
			v = new Vector();
			while (m_db.next())
			{
				pk = new BaseIntKey();
				pk.setID(m_db.getInt(1));
				v.addElement(pk);
//System.out.println(pk.getID());
			}
		  if (!m_bTransaction)
			  closeConnection();
		}
		catch(Exception e)
		{
		  if (!m_bTransaction)
			  closeConnection();
			throw new LinuxboxException(ExceptionHelper.TODO, "FIND", "Can't find record: " + e.getMessage());
		}
		return v.iterator();
	}

  public int getCountBySql(String sqlWhere) throws Exception
	{
    int count = 0;
    try
    {
			StringBuffer sql = new StringBuffer();
      sql.append("select count(id) from ");
      sql.append(getTableName());
      sql.append(sqlWhere);

			makeConnection();
			m_db.executeQuery(sql.toString());
			if (m_db.next())
        count = m_db.getInt(1);
		  if (!m_bTransaction)
			  closeConnection();
		}
		catch(Exception e)
		{
		  if (!m_bTransaction)
			  closeConnection();
 			throw new LinuxboxException(ExceptionHelper.TODO, "getCountBySql", "Can't find record: " + e.getMessage());
		}
    return count;
	}

}
